/** 
 * File-Name:CommonDao.java
 *
 * Created on 2010-12-21 上午11:08:51
 * 
 * @author: Neo (neolimeng@gmail.com)
 * Software Engineering Institute, Peking University, China
 * 
 * Copyright (c) 2009, Peking University
 * 
 *
 */
package neoutil;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.log4j.Logger;

import site.entity.Resource;


/**
 * Description:
 * 
 * @author: Neo (neolimeng@gmail.com) Software Engineering Institute, Peking
 *          University, China
 * @version 1.0 2010-12-21 上午11:08:51
 */
public class CommonDao<T, PK extends Serializable> {
	private static Logger log = Logger.getLogger(CommonDao.class);
	private Class<T> t;
	private Connection connection;
	
	public long getMaxId(String tableName)
	{
		List<T> ts = null;

		String sql = "SELECT * FROM "+ tableName +" ORDER BY id DESC LIMIT 1";
		try {
			ts = find(sql.toString());
			return ((CommonEntity)ts.get(0)).getId();
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		} finally {
		}		
	}
	
	public CommonDao(Class<T> t, Connection connection) {
		if (t == null || connection == null) {
			System.err.println("CommonDao中传入的参数为null！！");
			log.error("CommonDao中传入的参数为null！！");
		} else {
			this.t = t;
			this.connection = connection;
		}
	}

	public int countAll() {
		String sql = "SELECT COUNT(*) FROM " + t.getSimpleName().toLowerCase();
		return count(sql);
	}

	public int count(String sql, Object... params) {
		Long start = System.currentTimeMillis();
		Object result = null;
		ScalarHandler handler = new ScalarHandler();
		QueryRunner queryRunner = new QueryRunner();

		try {
			result = queryRunner.query(connection, sql, handler, params);
			return ((Long) result).intValue();
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		} finally {
			if (result != null) {
				log.debug("count查询到的Entity个数为：" + result + "，执行的时间为：" + (System.currentTimeMillis() - start) + "ms。SQL语句为：" + sql);
			} else {
				log.info("find未查询到任何Entity!");
			}
		}
	}

	
	
	public T getById(Long id) {
		return findUnique("id", id);
	}

	public T getByUuid(String uuid) {
		return findUnique("uuid", uuid);
	}

	public T findUnique(String criterion, Object param) {
		return findUnique(new String[] { criterion }, new Object[] { param });
	}

	public T findUnique(String[] criteria, Object[] params) {
		if (criteria.length <= 0 || params.length <= 0 || criteria.length != params.length) {
			log.error("查询条件参数错误，部分为空或者个数不一致");
			return null;
		} else {
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT * FROM " + t.getSimpleName() + " WHERE ");
			for (int i = 0; i < criteria.length; i++) {
				if (i < criteria.length - 1) {
					sql.append(criteria[i] + " = ?, ");
				} else {
					sql.append(criteria[i] + " = ? ");
				}
			}
			List<T> ts = find(sql.toString(), params);
			if (ts == null) {
				return null;
			} else if (ts.size() == 1) {
				return ts.get(0);
			} else {
				log.error("查询到的个数不唯一，无法得到唯一确定值！");
				return null;
			}
		}
	}

	public List<T> find(String criterion, Object param) {
		return find(new String[] { criterion }, new Object[] { param });
	}

	public List<T> find(String[] criteria, Object[] params) {
		if (criteria.length <= 0 || params.length <= 0 || criteria.length != params.length) {
			log.error("查询条件参数错误，部分为空或者个数不一致");
			return null;
		} else {
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT * FROM " + t.getSimpleName() + " WHERE ");
			for (int i = 0; i < criteria.length; i++) {
				if (i < criteria.length - 1) {
					sql.append(criteria[i] + " = ?, ");
				} else {
					sql.append(criteria[i] + " = ? ");
				}
			}
			return find(sql.toString(), params);
		}
	}

	public Page<T> find(Page<T> page, String criterion, Object param) {
		return find(page, new String[] { criterion }, new Object[] { param });
	}

	public Page<T> find(Page<T> page, String[] criteria, Object[] params) {
		if (criteria.length <= 0 || params.length <= 0 || criteria.length != params.length) {
			log.error("查询条件参数错误，部分为空或者个数不一致");
			return null;
		} else {
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT * FROM " + t.getSimpleName() + " WHERE ");
			for (int i = 0; i < criteria.length; i++) {
				if (i < criteria.length - 1) {
					sql.append(criteria[i] + " = ?, ");
				} else {
					sql.append(criteria[i] + " = ? ");
				}
			}
			return find(page, sql.toString(), params);
		}
	}

	public List<T> getAll() {
		String sql = "SELECT * FROM " + t.getSimpleName();
		return find(sql);
	}

	public Page<T> getAll(final Page<T> page) {
		String sql = "SELECT * FROM " + t.getSimpleName();
		return find(page, sql);
	}

	public void execute(String sql, Object... params) throws SQLException {
		Long start = System.currentTimeMillis();
		QueryRunner queryRunner = new QueryRunner();
		queryRunner.update(connection, sql, params);
		log.info("execute方法执行的时间为：" + (System.currentTimeMillis() - start) + "ms。SQL语句为" + sql);
	}

	public List<T> find(String sql, Object... params) {
		Long start = System.currentTimeMillis();
		List<T> entities = null;
		ResultSetHandler<List<T>> handler = new BeanListHandler<T>(t);
		QueryRunner queryRunner = new QueryRunner();

		try {
			entities = queryRunner.query(connection, sql, handler, params);

			return entities;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			if (entities != null && entities.size() >= 1) {
				log.info("find查询到的" + entities.get(0).getClass().getName() + "个数为：" + entities.size() + "，执行的时间为：" + (System.currentTimeMillis() - start) + "ms。SQL语句为：" + sql);
			} else {
				log.info("find未查询到任何Entity!");
			}
		}
	}

	public Page<T> find(final Page<T> page, String sql, Object... params) {
		Long start = System.currentTimeMillis();
		ResultSetHandler<List<T>> handler = new BeanListHandler(t);
		QueryRunner queryRunner = new QueryRunner();
		int totalNum = 0;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<T> entities = null;
		try {
			String countSql = "SELECT count(*) " + sql.substring(sql.toLowerCase().indexOf("from"));

			preparedStatement = connection.prepareStatement(countSql);
			queryRunner.fillStatement(preparedStatement, params);
			resultSet = preparedStatement.executeQuery();
			log.info(countSql);

			while (resultSet.next()) {
				totalNum = resultSet.getInt(1);
			}
			log.info("中间执行的时间为：" + (System.currentTimeMillis() - start) + "ms。");
			resultSet.close();
			preparedStatement.close();

			if (totalNum == 0) {
				return null;
			} else {
				page.setTotalCount(totalNum);
				int startNum = (page.getPageNo() - 1) * page.getPageSize();
				int count = page.getPageSize();
				sql += " limit " + startNum + "," + count;
				entities = queryRunner.query(connection, sql, handler, params);
				page.setResult(entities);

				return page;
			}

		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			if (totalNum != 0) {
				log.info("find查询到的" + entities.get(0).getClass().getName() + "个数为：" + page.getResult().size() + "，执行的时间为：" + (System.currentTimeMillis() - start) + "ms。SQL语句为：" + sql);
			} else {
				log.info("find未查询到任何Entity!");
			}
		}
	}
}
